﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Web.Library.DomainObject.SQLServer;
using System.Data;
using Dapper;
using System.Collections.ObjectModel;

namespace Web.Library.DataAccess.SQLServer
{
    public partial class SysGroupAuthorizationRepo
    {
        public List<SysMenu> RetrieveMenuAuthorizationByGroupIDAndParentID(int GroupID, int ParentID)
        {
            List<SysMenu> tt = new List<SysMenu>();
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    string strQuery = @"
                                        SELECT Menu.MenuID,
                                        Menu.Title,
                                        Menu.Url,
                                        Menu.Sort
                                        FROM sysGroupAuthorization GroupAuthorization
                                        INNER JOIN SysMenu Menu
                                        ON Menu.MenuID = GroupAuthorization.MenuID
                                        WHERE 
                                        GroupAuthorization.GroupID = @GroupID AND 
                                        Menu.ParentID = @ParentID AND AllowDisplay=1
                                        ORDER BY Menu.Sort";

                    tt = conn.Query<SysMenu>(strQuery, new { ParentID = ParentID,GroupID=GroupID }).ToList<SysMenu>();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return tt;
        }

        public SysGroupAuthorization RetrieveMenuAuthorizationByGroupIDAndMenuID(int GroupID, int MenuID)
        {
            SysGroupAuthorization tt = new SysGroupAuthorization();
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    string strQuery = @"
                                        SELECT AuthID, GroupID, MenuID, AllowDisplay, AllowUpdate, AllowCreate, AllowDelete, CreatedDate, CreteadBy, UpdatedDate, UpdatedBy FROM dbo.SysGroupAuthorization with(nolock) WHERE GroupID = @GroupID AND MenuID= @MenuID";

                    //tt = conn.Query<SysGroupAuthorization>(strQuery, new { MenuID = MenuID, GroupID = GroupID }).ToList<SysGroupAuthorization>();

                    tt = conn.Query<SysGroupAuthorization>(strQuery, new { MenuID = MenuID, GroupID = GroupID }).SingleOrDefault();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return tt;
        }

        public bool UpdateInsert(SysGroupAuthorization domain)
        {
            int result = 0;
            using (IDbConnection conn = base.GetConnection(connectionString))
            {
                //string sqlQuery = "UPDATE dbo.SysGroupAuthorization SET GroupID = @GroupID, MenuID = @MenuID, AllowDisplay = @AllowDisplay, AllowUpdate = @AllowUpdate, AllowDelete = @AllowDelete, CreteadBy = @CreteadBy, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE AuthID = @AuthID";

                string sqlQuery = @"
DECLARE @Exist int
    
    SELECT @Exist = COUNT(AuthID)
    FROM SysGroupAuthorization
    WHERE GroupID = @GroupID AND MenuID = @MenuID
    
	IF @Exist > 0
	BEGIN
		UPDATE SysGroupAuthorization SET
			--Rowstatus = @Rowstatus,
			AllowDisplay = @AllowDisplay,
			AllowCreate = @AllowCreate,
			AllowUpdate = @AllowUpdate,
			AllowDelete = @AllowDelete,
			UpdatedBy = @UpdatedBy,
			UpdatedDate = @UpdatedDate
		WHERE GroupID = @GroupID AND MenuID = @MenuID
		
		--SELECT 0 AS 'ID'
	END
	ELSE
	BEGIN
		INSERT INTO SysGroupAuthorization(
			--Rowstatus,
			GroupID,
			MenuID,
			AllowDisplay,
			AllowCreate,
			AllowUpdate,
			AllowDelete,
			CreteadBy,
			CreatedDate
		)
		VALUES(
			---0,
			@GroupID,
			@MenuID,
			@AllowDisplay,
			@AllowCreate,
			@AllowUpdate,
			@AllowDelete,
			@CreteadBy,
			@CreatedDate
		)
		
		--SELECT @@IDENTITY AS 'ID'
	END";

                try
                {
                    conn.Open();
                    result = conn.Execute(sqlQuery, domain);
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return (result > 0);
        }
        
    }


}
